﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Library.Entity;
using Library.Connection;
using System.Data.SqlClient;
using Library.SQL;
using System.Collections;
using System.Data;
using MySql.Data.MySqlClient;

namespace Library.Services
{
    public class MemberService
    {
        DataBaseConnectivity m_dataBaseConectivity = new DataBaseConnectivity();
        public MemberService()
        { }
        internal Member GetMemberById(int Id)
        {
            Member result = null;
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectMemberById(), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("Id", MySqlDbType.Int32).Value = Id;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new Member(rd);
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }
        internal Member GetMemberByMemberCode(string MemberCode)
        {
            Member result = null;
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectMemberByCode(), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("Code", MySqlDbType.VarChar).Value = MemberCode;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new Member(rd);
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }

        internal void InsertLog(Member m_Member, string p)
        {
          
        }

        internal IList GetAllMember()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectAllMember(), m_dataBaseConectivity.MySqlConnection);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new Member(rd));
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }

        internal void InsertNewMember(Member Member)
        {
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.InsertMember(), m_dataBaseConectivity.MySqlConnection);

                cmd.Parameters.Add("Code", MySqlDbType.VarChar).Value = Member.Code;
                cmd.Parameters.Add("Name", MySqlDbType.VarChar).Value = Member.Name;
                cmd.Parameters.Add("Remark", MySqlDbType.VarChar).Value = Member.Remark;
                cmd.Parameters.Add("CreatedDate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("ModifiedDate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("CreatedUserId", MySqlDbType.Int32).Value = Member.CreatedUserId;
                cmd.Parameters.Add("ModifiedUserId", MySqlDbType.Int32).Value = Member.ModifiedUserId;

                cmd.Parameters.Add("MemberTypeId", MySqlDbType.Int32).Value = Member.MemberTypeId;
                cmd.Parameters.Add("JoinDate", MySqlDbType.Datetime).Value = Member.JoinDate;
                cmd.Parameters.Add("Gender", MySqlDbType.Int32).Value = Member.Gender;
                cmd.Parameters.Add("Address", MySqlDbType.VarChar).Value = Member.Address;
                cmd.Parameters.Add("Phone", MySqlDbType.VarChar).Value = Member.Phone;
                cmd.Parameters.Add("IDCardNo", MySqlDbType.VarChar).Value = Member.IDCardNo;
                cmd.Parameters.Add("IDCardType", MySqlDbType.Int32).Value = Member.IDCardType;
                cmd.Parameters.Add("MaxBorrow", MySqlDbType.Int32).Value = Member.MaxBorrow;
                cmd.Parameters.Add("MemberClassId", MySqlDbType.Int32).Value = Member.MemberClassId;


                cmd.ExecuteNonQuery();
                cmd.CommandText = SQLScript.SelectMaxMemberId();
                cmd.Parameters.Clear();
                Member.Id = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
        }

        internal void UpdateMember(Member Member)
        {
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.UpdateMember(), m_dataBaseConectivity.MySqlConnection);

                cmd.Parameters.Add("Code", MySqlDbType.VarChar).Value = Member.Code;
                cmd.Parameters.Add("Name", MySqlDbType.VarChar).Value = Member.Name;
                cmd.Parameters.Add("Remark", MySqlDbType.VarChar).Value = Member.Remark;
                cmd.Parameters.Add("ModifiedDate", MySqlDbType.Datetime).Value = DateTime.Now;
                cmd.Parameters.Add("ModifiedUserId", MySqlDbType.Int32).Value = Member.ModifiedUserId;
                cmd.Parameters.Add("MemberTypeId", MySqlDbType.Int32).Value = Member.MemberTypeId;
                cmd.Parameters.Add("JoinDate", MySqlDbType.Datetime).Value = Member.JoinDate;
                cmd.Parameters.Add("Gender", MySqlDbType.Int32).Value = Member.Gender;
                cmd.Parameters.Add("Address", MySqlDbType.VarChar).Value = Member.Address;
                cmd.Parameters.Add("Phone", MySqlDbType.VarChar).Value = Member.Phone;
                cmd.Parameters.Add("IDCardNo", MySqlDbType.VarChar).Value = Member.IDCardNo;
                cmd.Parameters.Add("IDCardType", MySqlDbType.Int32).Value = Member.IDCardType;
                cmd.Parameters.Add("MaxBorrow", MySqlDbType.Int32).Value = Member.MaxBorrow;
                cmd.Parameters.Add("MemberClassId", MySqlDbType.Int32).Value = Member.MemberClassId;

                cmd.Parameters.Add("Id", MySqlDbType.Int32).Value = Member.Id;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
        }

        internal void DeleteMember(Member Member)
        {
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.DeleteMember(), m_dataBaseConectivity.MySqlConnection);
                cmd.Parameters.Add("Id", MySqlDbType.Int32).Value = Member.Id;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
        }

        internal int GetMaxMemberId()
        {
            int result = 0;
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectMaxMemberId(), m_dataBaseConectivity.MySqlConnection);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                object r = cmd.ExecuteScalar();
                result = Convert.ToInt32(r is DBNull ? 0 : r);
                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
        }

        internal string GenerateCode()
        {
            string result = "MBR0001";
            try
            {
                int maxMemberId = GetMaxMemberId();
                Member latestUser = GetMemberById(maxMemberId);
                if (latestUser == null)
                    return result;
                string lastCOde = latestUser.Code;
                
                int j = 0;
                for(int i=lastCOde.Length;i>-1;i--)
                {
                    string chr =  lastCOde.Substring(i-1,1);
                    int p = 0;
                    if (!int.TryParse(chr, out p))
                    {
                        j = i;
                        break;
                    }
                }
                string codeInt = lastCOde.Substring(j, lastCOde.Length - j);
                int pad = codeInt.Length;
                int code = Convert.ToInt32(codeInt);
                int nextCode = code + 1;
                string prefix = lastCOde.Substring(0, j);
                result = prefix + nextCode.ToString().PadLeft(pad,'0');

                Member found = GetMemberByMemberCode(result);
                while (found != null)
                {
                    nextCode++;
                    result = prefix + nextCode.ToString().PadLeft(pad, '0');
                    found = GetMemberByMemberCode(result);
                }

                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
        }

        internal IList GetMemeberByLike(string p)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.MySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(SQLScript.SelectAllMemberByLike(p), m_dataBaseConectivity.MySqlConnection);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new Member(rd));
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.MySqlConnection.Close();
            }
            return result;
        }
    }
}